Visier Formula Language (VFL)
Use the Visier Formula Language (VFL) to define metrics in the Visier platform.
This is a complete list of syntax and functions you can use to define your metrics. Each definition includes a description, real-life examples, and acceptable parameter types.
Tip: To learn more, take the Visier University eLearning course: Using Visier Formula Language.
Metric
A metric is a business concern that can be quantified as a number. For more information, see Work with Metrics.
Syntax
Use metric syntax to construct your metric formulas.
aggregate
Keyword to add an aggregation clause to a query. Data aggregation queries must have exactly one aggregation clause.
Note: For common aggregation types, see Aggregation.
Examples
Get the number of employees in your organization.
Parameter types: (Aggregation)
on Employee validUntil instant aggregate(count(Employee.EmployeeID))filterBy
Keyword to add an optional filter clause to a query. Use filterBy to specify the records that you want to include in the aggregation. You may use multiple filter clauses in a query. You may use existing properties, concepts, or dimensions in a filterBy clause.
Note: You can create a filter using other functions like include, exclude, hasValue, and operators like and, or, not, =, !=, <, <=, >, >=.
Examples
Get the number of high performers.
Parameter types: (Filter)
on Employee filterBy(isHighPerformer) aggregate(count(Employee.EmployeeID))lookup
Keyword to add a lookup clause to a query. This is used to find a value in a lookup overlay. Lookup overlays are pre-aggregated data sources and don't require an aggregation clause. Instead, the value can be looked up.
Examples
Look up the value of the Engagement Score on the Engagement overlay.
Parameter types: (Property)
on Engagement lookup(EngagementScore)on
Keyword to start a query on a data source. The data source is the subject, event, or overlay that the metric uses in its calculation, such as Employee or Applicant.
Examples
Get the total number of employees.
on Employee validUntil instant aggregate count(Employee.EmployeeID)
on Employee aggregate(count(Employee.EmployeeID))to
Keyword used after a via clause to specify the referenced data source. In a nested query, to specifies the outer query subject with which this nested query can be used.
Examples
Get the average number of recognitions received.
recognitionsReceived :=property(on Recognition via Receiver to Employee occurredIn(periodOf(instant)) aggregate(count(Recognition.Receiver.EmployeeID)))
on Employee aggregate(average(recognitionsReceived))values
Keyword to add a list aggregation clause to the query. Use values in a nested query to retrieve a list of values from the records returned by the outer query. The data point values are collected and returned in a list.
Note: The values query is executed once using an interval and the final instant. This means that for state queries, the query checks the final state, whereas for event queries, the query checks the entire history. Because values is primarily used to filter the records in the outer query, this function may lead to unexpected results if the outer query is not operating with the same time handling configuration as the nested query.
Examples
Get the number of current employees whose last name matches the surname of an active candidate.
Parameter types: (Property)
surnames :=on Candidate filterBy isActive values Last_Name
on Employee filterBy include(Last_Name, surnames) aggregate count(employeeID)via
Keyword to specify a subject reference in a nested query. Use via after the on clause to match records from the nested table with records from the outer table. via is required if the nested query and outer query are on different subjects. If via is omitted, the primary key for records on the nested table are matched with the key from outer query record. The parameter is a reference structure, which specifies the outer table and a "foreign key" column from the inner table with which to match outer query records.
Time Handling
Use metric time handling functions to retrieve records that meet specific time criteria. For example, you can retrieve the number of employees with a retirement date occurring within the context time interval.
forInstantsInInterval
Creates a time filter for data points valid up until each time instant within the time interval.
Examples
Get the average number of employees at monthly sampling points within the context time interval.
Parameter types: (TimeInterval, TimePeriod)
on Employee forInstantsInInterval(interval, months) aggregate(averageOverTime(count(Employee.EmployeeID)))forIntervalsInInterval
Creates a time filter for data points that occurred within each time interval within the time interval.
Examples
Get the number of physical attendance events by day where at least two employees were present.
Parameter types: (TimeInterval, TimePeriod)
on Attendance forIntervalsInInterval(interval, day) filterBy (Attendance.isPhysical) aggregate countOverIntervalsGT(Attendance.EmployeeID, 1)lastKnownStateByFilterIn
A time filter for data points that represent the last known state of an entity in the time interval. This is only applicable for subject queries. The filter applied in the function is exclusive, meaning any record that passes the filter is treated as inactive or invalid. This function is used with the last state of a subject to find closing states within the current period.
Note: This function returns the same value as "lastKnownStateIn" for valid records (those that do not pass the given filter) and returns the validity start date for invalid records (those that do pass the given filter).
Examples
Get the number of applicants that were in a valid state (isActiveApplicant) before reaching an invalid state (isClosedApplicant) at some point during the selected period.
Parameter types: (Filter, TimeInterval)
on Applicant lastKnownStateByFilterIn (isClosedApplicant, interval) aggregate count(ApplicantID) lastKnownStateIn
A time filter for data points that represent the last known state of an entity in the time interval. This is only applicable for subject queries. If a member's state record is valid at the end of the interval, only that record is selected and the effective date is the endpoint of that interval. Otherwise, the last state record whose validity interval intersects the given time interval is returned and its "validityEnd" is the effective date.
Note:
- Similar to validUntil, the lastKnownStateIn function identifies valid records. It is different from validUntil because it returns records that are valid at some point in the current time interval while validUntil only returns records valid to the end of the current time interval. It is also similar to validIn in that it examines all records intersecting the given time interval, but different in that it returns only the last record found.
- LastKnownStateIn is not supported in nested queries.
Examples
Get the number of applicants whose last known state record is in the time interval.
Parameter types: (TimeInterval)
on Applicant lastKnownStateIn(interval) aggregate(count(Applicant.ApplicantID))occurredIn
Creates a time filter for data points with a time instant property value that is within the time interval.
Examples
Get the total compensation that occurred within the selected time interval.
Parameter types: (TimeInterval)
on Compensation occurredIn(interval) aggregate(sum(Compensation.Amount))Get the number of employees expected to retire in the context time interval.
Parameter types: (TimeInterval, Property)
on Employee occurredIn(interval, Employee.ExpectedRetirementDate) aggregate(count(Employee.EmployeeID)) validIn
A time filter for data points that are valid in the time interval. This is only applicable for subject queries and within nested queries.
Note: Unlike other state table nested queries, the effective date is not inherited from the outer query, but is obtained from an optional field in the queried table. If no date field is given, sub-nested queries are not valid.
Examples
Get the number of applicants that spent less than 10 days in the Applied stage.
Parameter types: (TimeInterval)
Total_Days_in_Applied_Stage :=property(on Applicant validIn fromBeginningOfTime(instant) filterBy include(Normal_Process.Stage, "Applied") aggregate sum(daysInStateUntil(effectiveDate)))
on Applicant filterBy Total_Days_in_Applied_Stage < 10 aggregate count(ApplicantID) validUntil
A time filter for data points that are valid up until the time instant. This means "validUntil" returns records that are valid to the end of the selected time context. This function precedes a time instant function, such as "instant", "start", or "shift". This is only used for subject-based queries. If a time filter is not specified in a subject query, validUntil(instant) is used by default.
Aggregation
Use metric aggregation functions to return aggregate values, like average, sum, min, max.
average
Returns the average of the known values. In the metric settings, you can choose whether the average is returned as a percent, number, or other data type. Data points with an unknown value do not contribute to the average.
Examples
Get the average tenure at the organization.
Parameter types: (Property)
on Employee aggregate(average(Employee.Tenure))averageOverTime
Applies an aggregation function on time instants over a time interval, and returns the average of the results when using forInstantsInInterval or forIntervalsInInterval.
Examples
Get the average number of employees at monthly sampling points within the context time interval.
Parameter types: (Aggregation)
on Employee forInstantsInInterval(interval, months) aggregate(averageOverTime(count(Employee.EmployeeID)))count
Returns the number of known values. Data points with an unknown value do not contribute to the count.
Examples
Get the number of employees.
Parameter types: (Property)
on Employee aggregate(count(Employee.EmployeeID))countOverIntervalsGT
Applies a count function on time intervals over a time interval, and returns the sum on the results, excluding count values that do not exceed limit. This is used in conjunction with the time handling function forIntervalsInInterval.
Examples
Get the total physical attendance events where at least two employees were present for each day in the interval.
Parameter types: (Property, Number)
on Attendance forIntervalsInInterval(interval, day) filterBy (Attendance.isPhysical) aggregate countOverIntervalsGT(Attendance.EmployeeID, 1)distinct
Returns the number of unique values in the specified attribute. Data points with an unknown value do not contribute to the distinct count.
Note: Alternatively, you can use count. with filters to achieve the same result and takes less time to run.
Examples
Get the number of managers.
Parameter types: (Property)
on Employee aggregate(distinct(Employee.ManagerID))exists
Returns if there is any data matching the filters.
Examples
Get the number of candidates that have 2 or more applications.
Parameter types: (Property, Number)
Applications :=property(on Applicant via Candidate validUntil instant aggregate exists(Applicant.ApplicantID, 2))
on Candidate validUntil instant filterBy Applications > 0 aggregate count(Candidate.CandidateID)exp
Returns the exponential of the result of the given aggregation function.
Examples
Get the geometric mean of employee tenure.
Parameter types: (Aggregation)
on Employee aggregate(exp(sum(log(Employee.Tenure)) / count(Employee.EmployeeID))) log
Returns the natural logarithm of the result of the given aggregation function.
Examples
Get the geometric mean of employee tenure.
Parameter types: (Aggregation)
on Employee aggregate(exp(sum(log(Employee.Tenure)) / count(Employee.EmployeeID))) lowerQuartileRange
Returns the lower quartile of the known values. Data points with an unknown value do not contribute to the calculation.
Examples
Get the lower quartile employee pay level.
Parameter types: (Property)
on Employee aggregate(lowerQuartileRange(Employee.PayLevel))max
Returns the maximum known value. Data points with an unknown value do not contribute to the calculation.
Examples
Get the maximum employee tenure.
Parameter types: (Property)
on Employee aggregate(max(Employee.Tenure))median
Returns the median of the known values. Data points with an unknown value do not contribute to the calculation.
Examples
Get the median for employee pay level.
Parameter types: (Property)
on Employee aggregate(median(Employee.PayLevel))min
Returns the minimum known value. Data points with an unknown value do not contribute to the calculation.
Examples
Get the minimum employee tenure.
Parameter types: (Property)
on Employee aggregate(min(Employee.Tenure))modeHigh
Returns the mode of the known values. When multiple values are equally frequent, this functions returns the highest of those values. Data points with an unknown value do not contribute to the calculation.
Examples
Get the mode for employee age.
Parameter types: (Property)
on Employee aggregate(modeHigh(Employee.Age))modeLow
Returns the mode of the known values. When multiple values are equally frequent, this functions returns the lowest of those values. Data points with an unknown value do not contribute to the calculation.
Examples
Get the mode for employee age.
Parameter types: (Property)
on Employee aggregate(modeLow(Employee.Age))nPercentile
Returns the nth percentile of numeric values. The result may include decimal points due to interpolation when the desired percentile falls between two data points. Data points with an unknown value do not contribute to the calculation.
Examples
Compute the 90th percentile of the ages of all employees.
Parameter types: (Property, Number[0-100])
on Employee aggregate(nPercentile(Employee.Age, 90))standardDeviation
Returns the standard deviation. Data points with an unknown value do not contribute to the calculation.
Examples
Get the standard deviation of employee pay level.
Parameter types: (Property)
on Employee aggregate(standardDeviation(Employee.PayLevel))sum
Returns the sum of the values provided. Data points with an unknown value do not contribute to the calculation.
sumOverTime
Applies an aggregation function on time instants over a time interval, and returns the sum on the results when using forInstantsInInterval or forIntervalsInInterval.
Examples
Get the sum of total employees calculated monthly for the selected time interval.
Parameter types: (Aggregation)
on Employee forInstantsInInterval(interval, months) aggregate(sumOverTime(count(Employee.EmployeeID))) uniqueValue
Returns a value if all the values are the same, otherwise returns none.
Examples
Get the currency code for Compensation.CurrencyCode if they are all the same, otherwise return none.
Parameter types: (Property)
on Compensation aggregate(uniqueValue(Compensation.CurrencyCode))upperQuartileRange
Returns the upper quartile of the known values. Data points with an unknown value do not contribute to the calculation.
Transform
Use metric transform functions to return a version of your metric with additional qualifiers. For example, you can retrieve the annualized version of the employee exit rate metric.
annualize
Creates an annualized metric. This calculation is done based on the Gregorian calendar. This is not valid on a metric that is already annualized.
diffVsShiftedPeriod
Creates a metric that calculates the difference between this time period and previous time periods. This is only valid on a numeric metric. This calculation is based on the configured calendar.
Examples
Compare the difference in headcount between this period and last period.
Parameter types: (Metric, Number)
diffVsShiftedPeriod(Headcount, -1)else
Keyword to define a conditional statement that returns a metric to draw cell values from if the position doesn't pass the comparison check. This is used after an if clause.
Examples
Get positive growth rate values and replace non-positive growth rate values with the value 1.
if (growth_rate > 0) growth_rate else metric(1)exp
Creates a metric that calculates the exponential of a base metric.
Parameter types: (Metric)
filter
Creates a filtered metric using existing metrics and attributes. Use filter to filter the population of a metric by an attribute, such as a concept or dimension. The filter must be applicable to the metric. If the metric consists of multiple underlying metrics, the filter must be applicable to all underlying metrics.
Examples
Get the number of high performers.
Parameter types: (Metric, Filter)
filter(employeeCount, IsHighPerformer)if
Keyword for starting the definition of a conditional function that returns a metric with cell values from the corresponding metrics defined inside the if else blocks. If the comparison condition is N/A then the false metric value defined in the else block will be returned. This is used in conjunction with else.
Examples
Get positive growth rate values and replace non-positive growth rate values with the value 1.
if (growth_rate > 0) growth_rate else metric(1)log
Creates a metric that calculates the natural logarithm of a base metric.
Parameter types: (Metric)
max
Creates a metric whose value will return the larger value of the two supplied metrics.
Examples
Get the larger of metric growth_rate and 0.
Parameter types: (Metric, Metric)
max(growth_rate, metric(0))metric
Converts a query into a metric. This is used in nested queries. This allows you to avoid creating a net new metric in the solution by instead writing a metric into your metric formula.
Note: The nested metric is only valid in the formula it's written in, that is, you cannot reference a nested metric in a different metric formula.
Examples
Get the average number of women in the organization.
Parameter types: (Query)
femaleHeadcount :=metric(on Employee filterBy(IsFemale) aggregate(count(Employee.EmployeeID)))
headcount :=metric(on Employee aggregate(count(Employee.EmployeeID)))
femaleHeadcount / headcountmetricValueLookup
The value for this property is calculated by looking at the metric value for the specified hierarchy member(s) that the record belongs to.
Examples
Get the minimum compensation for the employee's job level.
Parameter types: (Metric, Hierarchy, String)
minimumPayLevel :=metric(on Employee validUntil instant aggregate min(Employee.Total_Cost_of_Workforce.Annual_Amount.Base_Pay))
metricValueLookup(metric(minimumPayLevel), Supervisory_Hierarchy, "Supervisory_Head")Get the minimum compensation for the employee's job level.
Parameter types: (Metric, Hierarchy)
minimumPayLevel :=metric(on Employee validUntil instant aggregate min(Employee.Total_Cost_of_Workforce.Annual_Amount.Base_Pay))
metricValueLookup(metric(minimumPayLevel), PayLevel)Get the minimum compensation for the employee's job level.
Parameter types: (Metric, Values, Values)
minimumPayLevel :=metric(on Employee validUntil instant aggregate min(Employee.Total_Cost_of_Workforce.Annual_Amount.Base_Pay))
metricValueLookup(metric(minimumPayLevel), list(PayLevel, Organization), list("", "OrgHead"))Get the minimum compensation for the employee's job level.
Parameter types: (Metric, Values)
minimumPayLevel :=metric(on Employee validUntil instant aggregate min(Employee.Total_Cost_of_Workforce.Annual_Amount.Base_Pay))
metricValueLookup(metric(minimumPayLevel), list(PayLevel))Get the minimum compensation for the employee's job level.
Parameter types: (Metric)
minimumPayLevel :=metric(on Employee validUntil instant aggregate min(Employee.Total_Cost_of_Workforce.Annual_Amount.Base_Pay))
metricValueLookup(metric(minimumPayLevel)))min
Creates a metric whose value will return the smaller value of the two supplied metrics.
Examples
Get the smaller amount when comparing total sick leave days and total vacation days.
Parameter types: (Metric, Metric)
min(leaveTotalDaysSickLeave, leaveTotalVacationDays)mtd
Transforms a metric to look at the month up to the end of each time period. This calculation is based on the configured calendar.
Examples
Get the number of employee exits for the month-to-date.
Parameter types: (Metric)
mtd(EmployeeExitCount)predictionMetric
Creates a metric that is associated with a prediction, which enables prediction analyses on this metric. The metric must be compatible with the prediction.
Examples
Associate a risk of exit metric with a compatible risk of exit prediction.
Parameter types: (Metric, String)
predictionMetric(AverageRiskOfExit, Employee.RiskOfExit)proportion
Creates a numeric metric that calculates the proportion between two metrics.
Examples
Get the proportion of female employees.
Parameter types: (Metric, Metric)
proportion(FemaleHeadcount, Headcount)qtd
Transforms a metric to look at the quarter up to the end of each time period. This calculation is based on the configured calendar.
Examples
Get the number of employee exits for the quarter-to-date.
Parameter types: (Metric)
qtd(EmployeeExitCount)ratioVsShiftedPeriod
Creates a metric that calculates the ratio between the current value and the value from a different time. This is only valid on a numeric metric. This calculation is based on the configured calendar.
Examples
Get the headcount ratio of this period and last period.
Parameter types: (Metric, Number)
ratioVsShiftedPeriod(Headcount, -1)shift
Creates a metric calculation that fluctuates by the selected time period. This calculation is based on the configured calendar.
Examples
Get the total compensation for the time period shifted backwards in years. If the selected time period is May to August 2020, this formula would then shift the time to May to August 2019.
Parameter types: (Metric, TimePeriod)
shift(TotalCompensation, back(year))shiftBack
Creates a time shifted metric. This calculation is based on the configured calendar.
Examples
Get the total compensation from a year ago.
Parameter types: (Metric, TimePeriod)
shiftBack(TotalCompensation, year)spanOfControl
Creates a metric that calculates the aggregate span of control, which is the number of subordinate objects that a superior object is responsible for. This is commonly used to calculate the number of employees reporting to a manager.
Examples
Get the average span of control for employees that are the head of an organization.
Parameter types: (Query, Hierarchy, String)
numDirectReports :=property(on Employee via Manager aggregate(count(Employee.EmployeeID)))
spanOfControl(on Employee aggregate(average(numDirectReports)), Organization, "OrgHead") toDouble
Creates a metric that converts a number into a decimal number. This is used to convert non-decimal numbers into decimal numbers prior to performing arithmetic to avoid decimal truncation.
Examples
Get the proportion of headcount in double format.
Parameter types: (Metric)
toDouble(employeeCountParameterized) / toDouble(employeeCount)trailing
Transforms a metric to look at the trailing period leading up to the end of each time period. This calculation is based on the configured calendar.
Examples
Get the number of employee exits for the trailing 15 months.
Parameter types: (Metric, TimePeriod)
trailing(EmployeeExitCount, months(15))trailing12Months
Transforms a metric to look at the trailing 12 months up to the end of each time period. This calculation is based on the configured calendar.
Examples
Get the number of employee exits for the trailing 12 months.
Parameter types: (Metric)
trailing12Months(EmployeeExitCount)wtd
Transforms a metric to look at the week up to the end of each time period. This calculation is based on the configured calendar.
Examples
Get the number of employee exits for the week-to-date.
Parameter types: (Metric)
wtd(EmployeeExitCount)ytd
Transforms a metric to look at the year up to the end of each time period. This calculation is based on the configured calendar.
Analytic Object
Analytic objects are the "things" that contain specific data. These objects represent what you want to analyze or aggregate data for, like applicants, employees, pay change events, interviews, and even employee skills. For more information, see Analytic Objects.
Event Transform
Use analytic object event transform functions to create different views for events. For example, retrieving an event state from a previous point in time.
priorState
Creates a prior-state view on an event. The resulting data points will be based on the state prior to the event occurrence, as opposed to the state after the event occurrence.
Subject State Changes
Use analytic object subject state changes functions to query the states that a subject goes through. For example, calculating the number of employees that have received an increase in pay by comparing the previous pay level to the current pay level.
changes
Creates an event view on a subject that returns each state change as a pair of events. One for the change-from event, i.e. change from the previous state, and another for the change-to event, i.e. change to the next state.
Examples
Get the number of employee state change events that occurred in the context time interval.
Parameter types: (Subject)
on changes(Employee) occurredIn(interval) aggregate(count(EmployeeID))conception
A change event filter for conception events. A conception event is a state with no immediate preceding state. Use "conception" to select records that had their first state in the time context. This is only valid for change views that include change-from events, such as previousChanges.
Examples
Get the number of new hires in the context time interval.
on changes(Employee) occurredIn(interval) filterBy(conception) aggregate(count(EmployeeID))hasChanged
Creates a filter for changes where the value for a hierarchy changed.
Examples
Get the number of times employees have had a change in manager.
Parameter types: (Property)
on previousChanges(Employee) occurredIn(interval) filterBy(hasChanged(Employee.ManagerID)) aggregate(count(Employee.EmployeeID))Get the number of times employees have changed their location.
Parameter types: (Hierarchy)
on previousChanges(Employee) occurredIn(interval) filterBy(hasChanged(Employee.Location)) aggregate(count(Employee.EmployeeID))nextChanges
Creates an event view on a subject that returns each change-to event.
Examples
Get the number of employee change-to events that occurred in the context time interval.
Parameter types: (Subject)
on nextChanges(Employee) occurredIn(interval) aggregate(count(Employee.EmployeeID)) previous
Creates a filter on the previous state of a change-from event.
Note:
- Returns unknown for conception events, termination events, and nextChanges.
- Throws an exception when using with any change views that do not include change-from events, or trying to use previous as the parameter value (eg, previous(previous(filter)).
Examples
Get the number of employees that have received an increase in pay.
Parameter types: (Property)
on previousChanges(Employee) occurredIn(interval) filterBy(previous(Employee.PayLevel) < Employee.PayLevel) aggregate(count(Employee.EmployeeID))Get the number of employees that changed from non-critical to critical.
Parameter types: (Filter)
on previousChanges(Employee) occurredIn(interval) filterBy(previous(!IsCritical) && IsCritical) aggregate(count(Employee.EmployeeID)) previousChanges
Returns event-like results where each event records a change from the previous state.
Note:
- Cannot be used with events or overlays.
- Use previous to retrieve the subject's state just before the change.
Examples
Get the number of changes on the Employee subject that occurred during the selected time period.
Parameter types: (Subject)
on previousChanges(Employee) occurredIn(interval) aggregate(count(Employee.EmployeeID)) termination
Creates a filter for the termination event. This is only valid for change events that include change-to events. Additionally, you can use termination as the end condition for traversals.
Note:
- Termination change events occur when the end state of a subject member does not have a subsequent state.
- Termination event date is the same as validityEnd date.
Examples
Get the employees that have an upcoming termination event.
on nextChanges(Employee) occurredIn(interval) filterBy(termination) aggregate(count(Employee.EmployeeID))Get the number of applicants that moved from Interviewing to Rejected or termination.
on traversals(Applicant) from(Interviewing) to(Rejected || termination) aggregate(count(Applicant.ApplicantID))If the subject's final state is Inactive until the end of time, you won't be able to retrieve terminations using the termination function. Instead, you can filter by isInactive.
on previousChanges(Employee) filterBy isInactive and not previous(isInactive) aggregate count(EmployeeID)Subject State Traversals
Use analytic object subject state traversals functions to capture the state changes a subject goes through. For example, calculate the number of applicants that have moved from "Interviewing" to "Hired".
excluding
Specifies the condition used for excluding traversal time when using traversals. Any time spent in a traversal while the condition is met is excluded.
Examples
Get the average number of days between the Interviewing stage and Hired stage, excluding time on hold.
Parameter types: (Filter)
on traversals(Applicant) from(Interviewing) to(Hired) excluding(OnHold) occurredIn(interval) aggregate(average(days(traversalDuration)))from
Specifies the start condition for traversals, and typically pairs with to which specifies the end condition.
Examples
Get the number of applicants that moved from the Interviewing stage to the Hired stage during the selected interval.
Parameter types: (Value)
on traversals(Applicant) from(Interviewing) to(Hired) occurredIn(interval) aggregate(count(Applicant.ApplicantID))starting
Creates a property that calculates on the starting state of a traversal, as opposed to the ending state of a traversal.
Examples
Get the number of applicants that have progressed by, at least, one stage.
Parameter types: (Property)
on traversals(Applicant) from(hasValue(Applicant.Stage)) to(Applicant.Stage > starting(Applicant.Stage)) aggregate(count(Applicant.ApplicantID))termination
A traversal ending condition for the termination state. A termination change event occurs if the end state of a subject member does not have an immediate and subsequent state.
Examples
Get the number of applicants that traversed from Interviewing to Rejected or termination.
Parameter types: (Property)
on traversals(Applicant) from(Interviewing) to(Rejected || termination) aggregate(count(Applicant.ApplicantID))to
Specifies the end condition for traversals, and typically pairs with from which specifies the start condition.
Examples
Get the number of applicants that moved from Interviewing to Hired during the selected time interval.
Parameter types: (Value)
on traversals(Applicant) from(Interviewing) to(Hired) occurredIn(interval) aggregate(count(Applicant.ApplicantID))Get the number of applicants that moved from Interviewing to the Hired or Rejected during the selected time interval.
Parameter types: (Value)
on traversals(Applicant) from(Interviewing) to(Hired || Rejected) occurredIn(interval) aggregate(count(Applicant.ApplicantID))Get the average number of days an applicant stays in the Interview stage in the selected time interval.
Parameter types: (Value)
on traversals(Applicant) from(Interviewing) to(!Interviewing || termination || instant) occurredIn(interval) aggregate(average(days(traversalDuration)))traversalDuration
Creates a property that returns the duration of the traversal in milliseconds. If the traversal query has an exclusion clause, the resulting duration will exclude time while the exclusion condition is met.
Note: This can only be used in a traversal query.
Examples
Get the average number of days it takes applicants to move from Interviewing to Hired.
on traversals(Applicant) from(Interviewing) to(Hired) aggregate(average(days(traversalDuration)))Get the average number of days it takes applicants to move from Interviewing to Hired, excluding any time that the applicant was on hold.
on traversals(Applicant) from(Interviewing) to(Hired) excluding(OnHold) aggregate(average(days(traversalDuration)))traversalDurationWorkingDays
Creates a property that returns the working days duration of the traversal in milliseconds. If the traversal query has an exclusion clause, the resulting duration will exclude time while the exclusion condition is met.
Note: This can only be used in a traversal query.
Examples
Get the average number of working days it takes applicants to move from Interviewing to Hired.
on traversals(Applicant) from(Interviewing) to(Hired) aggregate(average(days(traversalDurationWorkingDays)))Get the average number of working days it takes applicants to move from Interviewing to Hired, excluding any time that the applicant was on hold.
on traversals(Applicant) from(Interviewing) to(Hired) excluding(OnHold) aggregate(average(days(traversalDurationWorkingDays)))traversals
Keyword for starting a traversal view on a subject. A traversal is an event that occurs upon a successful transition from a starting state to an ending state, with potentially multiple successive intermediary states in between. The starting and ending states are specified through the accompanying from and to clauses.
Hierarchy
Dimensions organize unique values of an attribute into a list or a hierarchical structure for use as a filter or group by in your solution. For more information, see Dimensions.
As Property
Use these functions to extract hierarchical information as properties. For example, you can retrieve the display name of a parent object.
memberAtDepth
Creates a string property that returns the display name of the ancestor of the data point's member that is at the given depth.
Examples
Get the display name of the ancestor organization at level 2 of the hierarchy.
Parameter types: (Hierarchy, Number)
memberAtDepth(Organization, 2)normalized
Creates a numeric property that calculates the normalized value of the data point's member. This calculation is only valid on numeric hierarchies with only a single level.
Examples
Get the normalized performance level. Given a minimum level of 1, a maximum level of 5, and a data point's member level of 4, the resulting value will be 0.75.
Parameter types: (Hierarchy)
normalized(PerformanceLevel)Get the normalized performance level using a lower bound of 0. Given a maximum level of 5 and a data point's member level of 4, the resulting value will be 0.80.
Parameter types: (Hierarchy, Number)
normalized(PerformanceLevel, 0.0)numericDepth
Creates a numeric property that returns the minimum depth of the data point's owned org members. The depth of the hierarchy root is 0. The depth of an org head is their distance from root. The depth of an individual contributor is their org's depth plus one. This function is only supported by parent child hierarchies.
Examples
Get the lowest reporting level of an employee in the selected population.
Parameter types: (Hierarchy, String)
on Employee aggregate max(numericDepth(Supervisory_Hierarchy, "Supervisory_Head"))numericHeight
Creates a numeric property that returns the maximum height of the data point's owned org members. The height of the owner of a leaf member is 1. Where as the height of an individual contributor is 0. This function is only supported by parent child hierarchies.
Examples
Get the number of employees who are the lowest level managers of the supervisory hierarchy tree.
Parameter types: (Hierarchy, String)
on Employee filterBy(numericHeight(Supervisory_Hierarchy, "Supervisory_Head") = 1) aggregate(count(Employee.EmployeeID))ordinal
Creates a numeric property that returns the ordinal value of the data point's member. This calculation is only valid on hierarchies with only a single level.
Examples
Get the number of employee changes where performance improved.
Parameter types: (Hierarchy)
performanceLevelOrdinal :=ordinal(PerformanceLevel)
on changes(Employee) occurredIn(interval) filterBy(performanceLevelOrdinal > previous(performanceLevelOrdinal)) aggregate(count(Employee.EmployeeID))parent
Creates a string property that returns the display name of the parent member of the data point's member.
Examples
Get the display name of the parent organization.
Parameter types: (Hierarchy)
parent(Organization) topLevelAncestor
Creates a string property that returns the display name of the ancestor of the data point's member that is above the level threshold. If the data point's member is already above the level threshold, then its immediate parent is returned.
Examples
Get the display name of the ancestor organization above level 3 of the hierarchy. If the employee is in an organization at level 3 or below, then the ancestor at level 2 is returned. If the employee is in an organization at level 2 or above, then the immediate parent is returned.
Parameter types: (Hierarchy, Number)
topLevelAncestor(Organization, 3)Filter
Use hierarchy filter functions to include or exclude records from your query. For example, you can exclude employees belonging to a specific department from your query results.
exclude
Excludes records that do belong to specific hierarchy members.
Examples
Get the number of employees that do not belong to the R&D organization.
Parameter types: (Hierarchy, String)
on Employee filterBy(exclude(Organization, "R&D")) aggregate(count(Employee.EmployeeID)) Get the number of employees that do not reside in Vancouver and Seattle.
Parameter types: (Hierarchy, StringListValues)
on Employee filterBy(exclude(Location, list(list("Canada", "BC", "Vancouver"), list("USA", "WA", "Seattle")))) aggregate(count(Employee.EmployeeID))Get the number of employees that do not belong to the R&D and Product organizations.
Parameter types: (Hierarchy, Values)
on Employee filterBy(exclude(Organization, list("R&D", "Product"))) aggregate(count(Employee.EmployeeID))Get the number of employees that do not belong to the R&D and Product organizations.
Parameter types: (MemberSet)
on Employee filterBy(exclude(members(Organization, list("R&D", "Product")))) aggregate(count(Employee.EmployeeID))hasValue
Creates a filter for data points that have a known value. This does not return unknown values.
Examples
Get the number of employees that have a value for the Job Name attribute, which is a leveled dimension.
Parameter types: (Hierarchy)
Note: Specifying a hierarchy returns false for null values and values belonging to the hierarchy's "Unknown" member.
on Employee filterBy(hasValue(Employee.Job_Name)) aggregate(count(Employee.EmployeeID)) include
Includes records that belong to specific hierarchy members.
Examples
Get the number of employees in the R&D organization.
Parameter types: (Hierarchy, String)
on Employee filterBy(include(Organization, "R&D")) aggregate(count(Employee.EmployeeID)) Get the number of employees in Vancouver and Seattle.
Parameter types: (Hierarchy, StringListValues)
on Employee filterBy(include(Location, list(list("Canada", "BC", "Vancouver"), list("USA", "WA", "Seattle")))) aggregate(count(Employee.EmployeeID))Get the number of employees in the R&D and Product organizations.
Parameter types: (Hierarchy, Values)
on Employee filterBy(include(Organization, list("R&D", "Product"))) aggregate(count(Employee.EmployeeID))Get the number of employees in the R&D and Product organizations.
Parameter types: (MemberSet)
on Employee filterBy(include(members(Organization, list("R&D", "Product")))) aggregate(count(Employee.EmployeeID))userMember
Creates a filter containing the user's member. The user's member is based on the user's configuration. If this is not defined, then an empty filter is returned.
userOwnedOrgs
Creates a filter containing the hierarchy members that the user is the organization head of. If the user is head of no organizations, then an empty filter is returned.
Examples
Get the organization members that the user is a head of.
Parameter types: (Hierarchy, String)
userOwnedOrgs(Organization, 'Organization_Head')userParent
Creates a filter containing parents of the user's member value. A filter represents the parents of the user's most recent hierarchy. If a value is not defined, the returned filter is empty.
Examples
Get the parent of the user's organization member.
Parameter types: (Hierarchy)
Note: Accounts for the user's previous values of that hierarchy.
userParent(Organization)Get the parent of the user's organization member, taking into account the special case where the user is an organization head.
Parameter types: (Hierarchy, String)
Note: Does not account for the user's previous values of that hierarchy.
userParent(Organization, 'Organization_Head')userTopLevelAncestor
Creates a filter containing top level ancestor of the user's member value. A filter that represents the top level ancestor of the user's most recent hierarchy and does not account for the user's previous values of that hierarchy. If a value is not defined, the returned filter is empty.
Examples
Get the top level ancestor of the user's organization member.
Parameter types: (Hierarchy)
userTopLevelAncestor(Organization)Get the top level ancestor of the user's organization member, taking into account the special case where the user is an organization head.
Parameter types: (Hierarchy, String)
userTopLevelAncestor(Organization, 'Organization_Head')Member Set
Use hierarchy member set functions to create a subset of members that you can then use to filter your query results. For example, retrieving all members belonging to the Location hierarchy.
allMembers
Creates a member set containing all hierarchy members.
Examples
Get all the members of the location hierarchy.
Parameter types: (Hierarchy)
allMembers(Location) alphabetical
Sorts using the display name.
Examples
Get the first member when sorting VBSeries dimension members by display name in descending order.
topMember(VBSeries, alphabetical, descending)ascending
Sorts in ascending order.
Examples
Get the first member when sorting VBSeries dimension members by their ordinal in ascending order.
topMember(VBSeries, ordinal, ascending)descending
Sorts in descending order.
Examples
Get the first member when sorting VBSeries dimension members by their ordinal in descending order.
topMember(VBSeries, ordinal, descending)intersect
Creates a member set that is the intersection of two member sets.
Examples
Get the number of employees located in Canadian cities on the West Coast.
Parameter types: (MemberSet, MemberSet)
West_Coast_Cities := members(Employee.Location, List(List("North America", "Canada", "British Columbia", "Vancouver"), List("North America", "United States", "California", "Los Angeles")))
Canadian_Cities := members(Employee.Location, List(List("North America", "Canada", "British Columbia", "Vancouver"), List("North America", "Canada", "Ontario", "Toronto")))
on Employee filterBy include(intersect(West_Coast_Cities, Canadian_Cities)) aggregate count(EmployeeID)leaves
Creates a member set containing all the leaf members of the hierarchy. A leaf member is one that does not have any children.
Examples
Get all the leaf members of the location hierarchy.
Parameter types: (Hierarchy)
leaves(Location) members
Creates a member set containing the list of members. Only members that can be found are included.
Examples
Get a member from a hierarchy that does not have a fixed level structure.
Parameter types: (Hierarchy, String)
members(Organization, "R&D")Get a member from a hierarchy with a single level.
Parameter types: (Hierarchy, String)
members(PerformanceLevel, "Level1")Get members from a hierarchy with multiple levels.
Parameter types: (Hierarchy, StringListValues)
members(Location, list(list("Canada", "BC", "Vancouver"), list("USA", "WA", "Seattle"))) Get members from a hierarchy that does not have a fixed level structure.
Parameter types: (Hierarchy, String)
members(Organization, list("R&D", "Product"))Get members from a hierarchy with a single level.
Parameter types: (Hierarchy, Values)
members(PerformanceLevel, list("Level1", "Level2"))ordinal
Sort using the ordinal value.
Examples
Get the first member when sorting VBSeries dimension members by their ordinal in descending order.
performanceLevelOrdinal := ordinal(PerformanceLevel)
on changes(Employee) occurredIn(interval) filterBy(performanceLevelOrdinal > previous(performanceLevelOrdinal)) aggregate(count(Employee.EmployeeID))topLevel
Creates a member set containing all the top level members of the hierarchy. A top level member is a child of the root member.
Examples
Get all the top level members of the location hierarchy.
Parameter types: (Hierarchy)
topLevel(Location) topMember
Creates a member set containing the first member from the provided dimension sorted by the settings specified.
Note:
- The top member does not include unknown members.
- The top member is selected from the members at the highest level for multi-level dimensions.
Examples
Get the first member when sorting VBSeries dimension members by their ordinal in descending order.
Parameter types: (Hierarchy, Number, Number)
topMember(VBSeries, ordinal, descending)union
Creates a member set that is the union of two member sets.
Examples
Get employees located in either Canadian or West Coast cities.
Parameter types: (MemberSet, MemberSet)
West_Coast_Cities := members(Employee.Location, List(List("North America", "Canada", "British Columbia", "Vancouver"), List("North America", "United States", "California", "Los Angeles")))
Canadian_Cities := members(Employee.Location, List(List("North America", "Canada")))
on Employee filterBy include(union(West_Coast_Cities, Canadian_Cities)) aggregate count(EmployeeID)userPeers
Creates a filter containing siblings of the user's member value. This filter represents the peers of the user's most recent hierarchy and does not account for the user's previous values of that hierarchy. If a value is not defined, the returned filter is empty.
Examples
Get the siblings of the user's organization member.
Parameter types: (Hierarchy)
userPeers(Organization)Get the siblings of the user's organization member, taking into account the special case where the user is an organization head.
Parameter types: (Hierarchy, String)
userPeers(Organization, 'Organization_Head')Transform
Use hierarchy transform functions to convert dimensions and change it's behavior. For example, you can change the supervisor hierarchy to include the "Supervisor Head".
includeOrgHead
Transforms a parent child hierarchy to make it include the org head's records when evaluating the orgs that they own.
Property
Properties help you to describe or add information to analytic objects. For more information, see Properties.
Filter
Use hierarchy filter functions to include or exclude records from your query. For example, you can narrow the scope of your query to only include employees that are high performers.
exclude
Excludes records that do not have specific property values.
Examples
Get the number of employees that have a tenure that doesn't match a specific tenure value.
Parameter types: (Property, Number)
on Employee filterBy(exclude(Employee.Tenure, 1)) aggregate(count(Employee.EmployeeID)) Get the number of employees that have tenure that doesn't match a list of tenure values.
Parameter types: (Property, Numbers)
on Employee filterBy(exclude(Employee.Tenure, list(1, 2))) aggregate(count(Employee.EmployeeID)) Get the number of employees not managed by a high performer.
Parameter types: (Property, QueryValues)
highPerformers :=property(on Employee filterBy(IsHighPerformer) values(Employee.EmployeeID))
on Employee filterBy(exclude(Employee.ManagerID, highPerformers)) aggregate(count(Employee.EmployeeID)) Get the number of employees not managed by a specific employee.
Parameter types: (Property, String)
on Employee filterBy(exclude(Employee.ManagerID, "1001")) aggregate(count(Employee.EmployeeID)) Get the number of applicants that do not have a specific initial open date.
Parameter types: (Property, TimeInstant)
on Applicant filterBy(exclude(Applicant.InitialOpenDate, date("2009-06-01"))) aggregate(count(Applicant.ApplicantID))Get the number of applicants that do not have an initial open date that matches a list of specific dates.
Parameter types: (Property, TimeInstants)
on Applicant filterBy(exclude(Applicant.InitialOpenDate, List(date("2009-06-01"),date("2009-10-01")))) aggregate(count(Applicant.ApplicantID))Get the number of employees not managed by a list of specific employees.
Parameter types: (Property, Values)
on Employee filterBy(exclude(Employee.ManagerID, list("1001", "1002", "1003"))) aggregate(count(Employee.EmployeeID))hasValue
Creates a filter for data points that have a known value. This does not return unknown values.
Examples
Get the number of employees that have a value for Expected Promotion Date, which is a simple property.
Parameter types: (Property)
Note: Specifying a simple property returns all known values, including "Unknown" and empty values, but returns false for null values.
on Employee filterBy(hasValue(Employee.ExpectedPromotionDate)) aggregate(count(Employee.EmployeeID)) include
Includes records that have specific property values.
Examples
Get the number of employees with tenure matching a specific value.
Parameter types: (Property, Number)
on Employee filterBy(include(Employee.Tenure, 1)) aggregate(count(Employee.EmployeeID)) Get the number of employees with tenure matching values from a list.
Parameter types: (Property, Numbers)
on Employee filterBy(include(Employee.Tenure, list(1, 2))) aggregate(count(Employee.EmployeeID)) Get the number of employees managed by a high performer.
Parameter types: (Property, QueryValues)
highPerformers :=property(on Employee filterBy(IsHighPerformer) values(Employee.EmployeeID))
on Employee filterBy(include(Employee.ManagerID, highPerformers)) aggregate(count(Employee.EmployeeID)) Get the number of employees managed by a specific employee.
Parameter types: (Property, String)
on Employee filterBy(include(Employee.ManagerID, "1001")) aggregate(count(Employee.EmployeeID)) Get the number of applicants with a specific initial open date.
Parameter types: (Property, TimeInstant)
on Applicant filterBy(include(Applicant.InitialOpenDate, date("2009-06-01"))) aggregate(count(Applicant.ApplicantID))Get the number of applicants with initial open date matching values from a list.
Parameter types: (Property, TimeInstants)
on Applicant filterBy(include(Applicant.InitialOpenDate, List(date("2009-06-01"),date("2009-10-01")))) aggregate(count(Applicant.ApplicantID))Get the number of employees managed by an employee from a list of employees.
Parameter types: (Property, Values)
on Employee filterBy(include(Employee.ManagerID, list("1001", "1002", "1003"))) aggregate(count(Employee.EmployeeID))Transform
Use property transform functions to derive a property from one or more properties and other inputs. For example, you can compare two numeric property values and return the higher number.
collectFirstIfAnyExists
Creates a property that picks the first property with data in a list of specified properties. All properties should be of the same type.
Examples
Get the most recent start date from Employee Start. If Employee Start has no data available, get the most recent start date from Employee instead.
employment_start_date := property(on Employment_Start occurredIn fromBeginningOfTime(instant) aggregate max(effectiveDate))
collectFirstIfAnyExists(listAny(employment_start_date, Employee.Start_Date))convertCurrency
Create a numeric property that performs currency conversion. The currency is converted given a string attribute holding the source currency code and an implicit time value. The implicit time instant comes from the query end date and is used to look up the conversion rate at that instant.
Note: Currency conversion data must be available in the data version to perform conversions.
Examples
Get the total compensation amount in USD.
Parameter types: (Property, Property, String)
on Compensation aggregate(sum(convertCurrency(Compensation.Amount, Compensation.CurrencyCode, "USD"))) Get the total compensation amount in the configured currency code.
Parameter types: (Property, Property)
non Compensation aggregate(sum(convertCurrency(Compensation.Amount, Compensation.CurrencyCode))) currentStateDuration
Creates a property that calculates the time (in milliseconds) since the last change in property value.
Examples
Get the average number of days applicants spend in the current stage.
Parameter types: (Property)
on Applicant aggregate(average(days(currentStateDuration(Applicant.Stage))))Get the average number of days applicants spend in the current stage where the applicant is not on hold.
Parameter types: (Property, Filter)
on Applicant aggregate(average(days(currentStateDuration(Applicant.Stage, !OnHold)))) currentStateStart
Creates a property that calculates the time instant when the property changed to its current value.
Examples
Get the average number of days applicants spend in the current stage.
Parameter types: (Property)
on Applicant aggregate(average(daysBetween(currentStateStart(Applicant.Stage), effectiveDate))) Get the average number of days hired applicants spend waiting to start.
Parameter types: (Property, Filter)
on Applicant aggregate(average(daysBetween(currentStateStart(Applicant.Stage, Hired && ReadyToStart), effectiveDate)))else
Keyword to define a conditional statement that returns the value of a given property if the data point does not pass any preceding filters. This is used with if.
Examples
Get the bonus an employee should receive if they are top talent, a manager or neither.
baseBonusAmount :=Employee.Salary * 0.1
topTalentBonusAmount :=2.0 * baseBonusAmount
managerBonusAmount :=1.5 * baseBonusAmount
if (IsTopTalent) topTalentBonusAmount else if (IsManager) managerBonusAmount else baseBonusAmountexp
Creates a numeric property that calculates the exponential of a number.
Examples
Get the geometric mean of employee tenure.
Parameter types: (Property)
on Employee aggregate exp(sum(Log(age))/count(EmployeeID))if
Keyword for starting the definition of a conditional function that returns the value of a property if the data point passes a given filter. This is used in conjunction with else.
Examples
Get the bonus an employee should receive if they are top talent, a manager or neither.
baseBonusAmount :=Employee.Salary * 0.1
topTalentBonusAmount :=2.0 * baseBonusAmount
managerBonusAmount :=1.5 * baseBonusAmount
if (IsTopTalent) topTalentBonusAmount else if (IsManager) managerBonusAmount else baseBonusAmount log
Creates a numeric property that calculates the natural logarithm of a number.
Examples
Get the geometric mean of employee ages.
Parameter types: (Property)
on Employee aggregate Exp(sum(Log(age))/count(EmployeeID))max
Returns the maximum between two numbers.
Examples
Get the larger of the two when comparing the sum of sick pay and retirement and savings.
Parameter types: (Property, Property)
SickPay_PerEmployee := property(on Compensation_Payout occurredIn periodOf(instant) filterBy(Compensation_Payout.Payout_Type = "SickPay") aggregate sum(Compensation_Payout.Payout_Amount))
Savings_PerEmployee := property(on Compensation_Payout occurredIn periodOf(instant) filterBy(Compensation_Payout.Payout_Type = "Retirement and Savings") aggregate sum(Compensation_Payout.Converted_Pay_Amount))
max(SickPay_PerEmployee, Savings_PerEmployee)min
Returns the minimum between two numbers.
Examples
Get the smaller of the two when comparing the sum of sick pay and retirement and savings.
Parameter types: (Property, Property)
SickPay_PerEmployee := property(on Compensation_Payout occurredIn periodOf(instant) filterBy(Compensation_Payout.Payout_Type = "SickPay") aggregate sum(Compensation_Payout.Payout_Amount))
Savings_PerEmployee := property(on Compensation_Payout occurredIn periodOf(instant) filterBy(Compensation_Payout.Payout_Type = "Retirement and Savings") aggregate sum(Compensation_Payout.Converted_Pay_Amount))
min(SickPay_PerEmployee, Savings_PerEmployee)noValue
Creates an empty property based on the specified property type.
Examples
If an employee has no direct reports, return no value for the employee. Otherwise, return the number of direct reports.
Parameter types: (Property, Property)
if (Employee.Span_Of_Control = 0)
noValue(Employee.Span_Of_Control)
else
Employee.Span_Of_Controlproperty
Converts a query into a calculated property. This is used in nested queries. This allows you to avoid creating net new properties in the solution by writing a property into your metric formula.
Note: The nested property is only valid in the formula it's written in, that is, you cannot reference a nested property in a different metric formula.
Examples
Get the average compensation amount for employees in the organization.
Parameter types: (Query)
compensationAmount :=property(on Compensation occurredIn(interval) aggregate(sum(Compensation.Amount)))
on Employee aggregate(average(compensationAmount))sigmoid
Creates a numeric property that applies the standard logistic function on a numeric value, producing a value between 0 and 1 along a smooth S-curve. Large positive values are mapped closer to 1. Large negative values are mapped closer to 0.
Examples
Get the standardized employee age property, with an age of 20 being the 5% value, an age of 30 being the 50% value, and an age of 60 being the 95% value.
Parameter types: (Property, Property, Property, Property)
sigmoid(Employee.Age, 20, 30, 60)Get the standardized employee age property, with an age of 20 being the 5% value, and an age of 60 being the 95% value.
Parameter types: (Property, Property, Property)
sigmoid(Employee.Age, 20, 60)sumIfAnyExists
Creates a numeric property that calculates the sum of values, ignoring any unknown or non-existent values.
Examples
Get the average employee base pay and bonus, where bonus may not exist for some employees.
Parameter types: (Values)
on Employee aggregate(average(sumIfAnyExists(listAny(Employee.BasePay, Employee.Bonus)))) toDoubleAttribute
Explicitly converts a decimal number into a decimal property. This is usually not necessary as the conversion is done implicitly.
Parameter types: (Number)
toDoubleProperty
Creates a numeric property that converts a number into a decimal number. This is used to convert non-decimal numbers into decimal numbers prior to performing arithmetic to avoid decimal truncation.
Parameter types: (Property)
Time
Time formulas are helpful when working with date and time attributes. For example, you can shift a date forward or backwards by a number of days, weeks, months, or years.
attribute
Converts a time instant into a time instant property. This is usually not necessary as the conversion is done implicitly.
Examples
Get the number of days between an employee's start date and the selected time instant.
Parameter types: (TimeInstant)
daysBetween(Employee.StartDate, attribute(instant))back
Shift the time instance forward or backward for a time period..
Note: You can use a negative value to go forward in time.
combine
Adds two time periods together.
Examples
Create a period of 1 year and 6 months.
Parameter types: (TimePeriod, TimePeriod)
combine(year, months(6))date
Returns a time instant described by the input string. This string input must comply with ISO 8601.
day
Creates a time period of 1 day.
dayOfWeek
Creates a numeric property that returns which day of the week a time instant property falls on, given the time zone.
Examples
Get the day of the week where an employee card swipe event takes place in their local time zone, based on the Gregorian calendar.
Parameter types: (Property, Property, Number)
dayOfWeek(CardSwipe.EventDate, CardSwipe.TimeZone, gregorian)Get the day of the week where an employee card swipe event takes place in their local time zone, based on the configured calendar.
Parameter types: (Property, Property)
dayOfWeek(CardSwipe.EventDate, CardSwipe.TimeZone)days
Creates a time period of a given number of days.
Examples
Shift the selected time instant forward by 365 days.
Parameter types: (Number)
shift(instant, days(365))days
Creates a numeric property that convert milliseconds into days as a decimal number.
Examples
Get the average of applicants in days spent in the current stage.
Parameter types: (Property)
on Applicant aggregate(average(days(currentStateDuration(Applicant.Stage))))daysBetween
Creates a numeric property that calculates the number of days between two time instants. The earlier date should come first in the formula.
Note: If the start date is in the middle of a day and end date is in the middle of another day, and the two incomplete days form a full day, daysBetween counts that as one day.
Examples
Get the number of days between an employee's start date and effective date, based on the configured calendar.
Parameter types: (Property, Property)
daysBetween(Employee.StartDate, effectiveDate)Get the number of days between an employee's start date and effective date, based on the Gregorian calendar.
Parameter types: (Property, Property, Number)
daysBetween(Employee.StartDate, effectiveDate, gregorian)earlier
Returns the earlier of two time instants.
Examples
Get the number of employees as of the current instant or as of January 1 of 2016, whichever comes first.
Parameter types: (TimeInstant, TimeInstant)
on Employee validUntil earlier(instant, date("2016-01-01")) aggregate count(EmployeeID)earliest
Returns the earliest date for which data is available.
Examples
Get the sum of compensation amount for compensation events that occurred across all time.
on Compensation occurredIn(interval(earliest, instant)) aggregate(sum(Compensation.Amount)) effectiveDate
Returns the time instant at which the data point is observed. Use effectiveDate to specify the time at which the query's records should be calculated.
Note:
- For an event record, effectiveDate comes from the event date. Normally, the instant of the event is the start of day, including PriorState event tables and those based on ending event tables, however, certain ending event tables give effectiveDate as the end of day (or, start of day on the next day).
- For a state record in a nested query, effectiveDate is inherited from the outer query record, except for the validIn time filter with a dateField.
-
For a state record in an outer query, the way effectiveDate functions depends on the time filter:
-
validUntil gives the timeInstant parameter from the time filter.
-
forInstantsInInterval gives the instant in the interval for which the record has been selected. The same record may be used at different instants with different effective dates.
-
lastKnownStateIn gives the earlier of either the validityEnd from the record or the end of the timeInterval parameter.
-
lastKnownStateByFilterIn returns the same as lastKnownStateIn above for Open records (those that do not pass the given filter), and returns the validityStart date for Closed records (those that do pass the given filter).
-
occurredIn always gives latest, the end-of-data time, as it returns information about the future based on the last data available.
-
- If no time filter is specified in a nested query, either validUntil or "validAt(effectiveDate)" are used in the nested query, dependent on the outer query.
Caution: Because effectiveDate is inherited from the outer query, further nested queries on a nested query state record will not relate to the validity interval of that nested record, but will return results relevant to the outer query date. However, sub-nested queries are not valid for validIn queries without an explicit dateField.
Tip: For time-sensitive calculations such as Age, outside of time filters, "effectiveDate" provides the current time for the current record to use in calculations. This is not available inside time filters, where "instant" gives the current time from the display time axis.
Examples
Get the average number of days between the employee's last promotion and the effective date using the Gregorian calendar.
on Employee aggregate(average(daysBetween(Employee.LastPromotionDate, effectiveDate, gregorian))) end
Returns the end of a time interval.
Examples
Get the number of days between the selected period. If the selected period is January, then the calculation returns 31 days.
Parameter types: (TimeInterval)
daysBetween(start(interval), end(interval))expectedTotalWorkingHours
Creates a metric that calculates the total expected working hours of a full-time employee within the context time interval.
Note: This calculation is based on the configured calendar. If the expected working hours per day is not defined, then 7.5 is used.
Examples
Get the average compensation per FTE.
totalCompensation := metric(on Compensation_Payout occurredIn interval filterBy(isTCOWActualBasePay) aggregate sum(convertCurrency(Compensation_Payout.Payout_Amount, Compensation_Payout.Pay_Currency_Code)))
totalHoursWorked := metric(on Compensation_Payout occurredIn interval aggregate sum(Compensation_Payout.Payout_Hours))
fte := totalHoursWorked / expectedTotalWorkingHours
totalCompensation / fteextend
Creates an extended time interval from two time intervals. The resulting interval starts from the start of the first time interval, and ends at the end of the second time interval.
Examples
Get the number of exits that occurred in the last two periods.
Parameter types: (TimeInterval, TimeInterval)
on Employee_Exit occurredIn extend(interval, back(period)) aggregate count(Employee_Exit.EmployeeID)Get the total compensation for all compensation events that occurred in the context time interval and 2 additional months after.
Parameter types: (TimeInterval, TimePeriod)
on Compensation occurredIn(extend(interval, months(2))) aggregate(sum(Compensation.Amount)) fromBeginningOfTime
Creates a time interval from the earliest date for which data is available.
Examples
Get the total compensation for all compensation events that occurred from the start of data and up to the context time instant.
Parameter types: (TimeInstant)
on Compensation occurredIn(fromBeginningOfTime(instant)) aggregate(sum(Compensation.Amount)) gregorian
Use the Gregorian calendar, as opposed to the configured calendar.
Examples
Get the average number of months since employees were last promoted, using the Gregorian calendar.
on Employee aggregate(average(monthsBetween(Employee.LastPromotionDate, effectiveDate, gregorian))) instant
A time filter that specifies the current point in time according to the time context.
Note:
- For outer queries, this corresponds to the end of the selected time period.
- For nested queries, this corresponds to the effective date of the parent data point in the outer query.
Examples
Get the total number of employees in the population up until the end of the selected time context.
on Employee validUntil(instant) aggregate(count(Employee.EmployeeID))interval
A time filter that specifies the context time interval. This corresponds to the selected time interval. This is only valid when used within an outer query, and is not valid when used within a nested query.
Examples
Get the total compensation for a specific time period.
on Compensation occurredIn(interval) aggregate(sum(Compensation.Amount))interval
Creates a time interval starting from a time instant, and ending at another time instant.
Examples
Get the total compensation for all compensation events that occurred between two arbitrary time instants.
Parameter types: (String, String)
on Compensation occurredIn(interval("2010-01-01", "2011-01-01")) aggregate(sum(Compensation.Amount)) Get the total compensation for all compensation events that occurred from the start of data and up to the context time instant.
Parameter types: (TimeInstant, TimeInstant)
on Compensation occurredIn(interval(earliest, instant)) aggregate(sum(Compensation.Amount)) Get the total compensation for all compensation events that occurred in the 12 months leading up to the context time instant.
Parameter types: (TimeInstant, TimePeriod)
on Compensation occurredIn(interval(instant, back(months(12)))) aggregate(sum(Compensation.Amount)) Get the total compensation for all compensation events that occurred in the 12 months leading up to the context time instant.
Parameter types: (TimePeriod, TimeInstant)
on Compensation occurredIn(interval(months(12), instant)) aggregate(sum(Compensation.Amount)) later
Returns the later of two time instants.
Examples
Get the number of employees as of the current instant or as of January 1 of 2016, whichever comes later.
Parameter types: (TimeInstant, TimeInstant)
on Employee validUntil later(instant, date("2016-01-01")) aggregate count(EmployeeID)latest
Returns the latest date for which data is available.
Examples
Get the total compensation for compensation events that occurred across all time.
on Compensation occurredIn(interval(earliest, latest)) aggregate(sum(Compensation.Amount)) millisBetween
Creates a numeric property that calculates the number of milliseconds between two time instants.
Examples
Get the milliseconds between an employee's start date and effective date, based on the configured calendar.
Parameter types: (Property, Property)
millisBetween(Employee.StartDate, effectiveDate)Get the milliseconds between an employee's start date and effective date, based on the Gregorian calendar.
Parameter types: (Property, Property, Number)
millisBetween(Employee.StartDate, effectiveDate, gregorian)millisOfDay
Creates a property that returns the milliseconds from the start of day of a time instant property, given the time zone.
Examples
Get the milliseconds of the day that an employee card swipe event takes place in their local time zone.
Parameter types: (Property, Property)
millisOfDay(CardSwipe.EventDate, CardSwipe.TimeZone)month
Creates a time interval for the month of the time instant. This calculation is based on the configured calendar.
Examples
Get the total compensation for all compensation events that occurred in the month of the context time instant.
Parameter types: (TimeInstant)
on Compensation occurredIn(month(instant)) aggregate(sum(Compensation.Amount))months
Returns a time period of a given number of months.
Examples
Shift the context time instant forward by 12 months.
Parameter types: (Number)
shift(instant, months(12))monthsBetween
Creates a numeric property that calculates the number of months between two time instants.
Examples
Get the number of months between an employee's start date and effective date, based on the configured calendar.
Parameter types: (Property, Property)
monthsBetween(Employee.StartDate, effectiveDate)Get the number of months between an employee's start date and effective date, based on the Gregorian calendar.
Parameter types: (Property, Property, Number)
monthsBetween(Employee.StartDate, effectiveDate, gregorian)monthsInCalendarYear
Returns the number of months in a calendar year, based on the configured calendar.
Examples
Get the average monthly total compensation amount over the last 12 months.
on Compensation occurredIn(trailing12Months(instant)) aggregate(sum(Compensation.Amount) / monthsInCalendarYear)mtd
Returns a time interval from the beginning of the month and up to the time instant. This calculation is based on the configured calendar.
Examples
Get the total compensation for all compensation events that occurred from the beginning of the month and up to the context time instant.
on Compensation occurredIn(mtd) aggregate(sum(Compensation.Amount))Get the total compensation for all compensation events that occurred from the beginning of the month and up to the context time instant.
Parameter types: (TimeInstant)
on Compensation occurredIn(mtd) aggregate(sum(Compensation.Amount))period
Returns the time context. This corresponds to the time period a user selects in a visualization.
Note: The period value is always a positive integer representing the time period, followed by the time axis value, such as month or quarter. For example, a period query could use the value "3 years".
Examples
Shift the selected time in the visualization backward or forward by the selected time period from the current instant.
shift(instant, period)periodOf
Creates a time interval for the time period context of the time instant. This calculation is based on the configured calendar. The time period is user-selected and can be a week, month, quarter, year, or a time range.
Note: If the instant is the same as the beginning of the interval, periodOf adjusts the beginning of the interval to the following time period.
Examples
Get the total compensation amount that occurred during the time period for the current instant.
Parameter types: (TimeInstant)
on Compensation occurredIn(periodOf(instant)) aggregate(sum(Compensation.Amount))periodToDate
Creates a time interval from the beginning of the context time period to the context time instant. This calculation is based on the configured calendar.
Examples
Get the total compensation for all compensation events that occurred from the beginning of the context time period and up to the context time instant.
on Compensation occurredIn(periodToDate) aggregate(sum(Compensation.Amount))Get the total compensation for all compensation events that occurred from the beginning of the context time period and up to the context time instant.
Parameter types: (TimeInstant, TimePeriod)
on Compensation occurredIn(periodToDate(instant, period)) aggregate(sum(Compensation.Amount)) Get the total compensation for all compensation events that occurred from the beginning of the context time period and up to the context time instant.
Parameter types: (TimeInstant)
on Compensation occurredIn(periodToDate(instant)) aggregate(sum(Compensation.Amount))Get the total compensation for all compensation events that occurred from the beginning of the context time period and up to the context time instant.
Parameter types: (TimePeriod, TimeInstant)
on Compensation occurredIn(periodToDate(period, instant)) aggregate(sum(Compensation.Amount))Get the total compensation for all compensation events that occurred from the beginning of the context time period and up to the context time instant.
Parameter types: (TimePeriod)
on Compensation occurredIn(periodToDate(period)) aggregate(sum(Compensation.Amount)) qtd
Creates a time interval from the beginning of the quarter and up to the time instant. This calculation is based on the configured calendar.
Examples
Get the total compensation for all compensation events that occurred from the beginning of the quarter and up to the context time instant.
on Compensation occurredIn(qtd) aggregate(sum(Compensation.Amount))Get the total compensation for all compensation events that occurred from the beginning of the quarter and up to the context time instant.
Parameter types: (TimeInstant)
on Compensation occurredIn(qtd(instant)) aggregate(sum(Compensation.Amount))quarter
Returns a time period of 1 quarter.
quarter
Creates a time interval for the quarter of the time instant. This calculation is based on the configured calendar.
Examples
Get the total compensation for all compensation events that occurred in the quarter of the context time instant.
Parameter types: (TimeInstant)
on Compensation occurredIn(quarter(instant)) aggregate(sum(Compensation.Amount))quarters
Creates a time period of a given number of quarters.
Examples
Shift the context time instant forward by 4 quarters.
Parameter types: (Number)
shift(instant, quarters(4))shift
Creates a time instant property that shifts a time instant by a time period.
Examples
Get the number of employees who started less than a year ago, based on the Gregorian calendar.
Parameter types: (Property, TimePeriod, Number)
on Employee filterBy(effectiveDate < shift(Employee.StartDate, year, gregorian)) aggregate(count(Employee.EmployeeID))Get the number of employees who started at the organization for the time period shifted backwards in years. If the selected time period is May to August 2020, this formula would then shift the time to May to August 2019.
Parameter types: (Property, TimePeriod)
on Employee filterBy(effectiveDate < shift(Employee.StartDate, year)) aggregate(count(Employee.EmployeeID))Shift the context time instant forward by 1 year.
Parameter types: (TimeInstant, TimePeriod)
shift(instant, years(1))Shift the context time instant back by 12 months.
Parameter types: (TimeInstant, TimePeriod)
shift(instant, back(months(12)))Get the total compensation that occurred during the period, shifted backwards in time by 12 months. If the selected time period is May to August 2020, this formula would then shift the time to May to August 2019.
Parameter types: (TimeInterval, TimePeriod)
on Compensation occurredIn(shift(interval, back(months(12)))) aggregate(sum(Compensation.Amount)) shiftBack
Creates a time instant property that shifts a time instant back by a time period. This calculation is based on the configured calendar.
Examples
Get the number of employees who started less than a year ago.
Parameter types: (Property, TimePeriod)
on Employee filterBy(Employee.StartDate > shiftBack(effectiveDate, year)) aggregate(count(Employee.EmployeeID))start
Returns the start of a time interval.
Examples
Get the number of employees that are valid at the start of the interval.
Parameter types: (TimeInterval)
on Employee validUntil(start(interval)) aggregate(count(Employee.EmployeeID))trailing
Creates a time interval from the beginning of the trailing period leading up to the time instant. This calculation is based on the configured calendar.
Examples
Get the total compensation for all compensation events that occurred in the last 15 months leading up to the context time instant.
Parameter types: (TimeInstant, TimePeriod)
on Compensation occurredIn(trailing(instant, months(15))) aggregate(sum(Compensation.Amount)) Get the total compensation for all compensation events that occurred in the last 15 months leading up to the context time instant.
Parameter types: (TimePeriod)
on Compensation occurredIn(trailing(months(15))) aggregate(sum(Compensation.Amount)) trailing12Months
Creates a time interval of the trailing 12 months leading up to the context time instant. This calculation is based on the configured calendar.
Examples
Get the total compensation for all compensation events that occurred in the last 12 months leading up to the context time instant.
on Compensation occurredIn(trailing12Months) aggregate(sum(Compensation.Amount))Get the total compensation for all compensation events that occurred in the last 12 months leading up to the context time instant.
Parameter types: (TimeInstant)
on Compensation occurredIn(trailing12Months(instant)) aggregate(sum(Compensation.Amount)) week
Creates a time period of 1 week.
week
Creates a time interval for the week of the time instant. This calculation is based on the configured calendar.
Examples
Get the total compensation for all compensation events that occurred in the week of the context time instant.
Parameter types: (TimeInstant)
on Compensation occurredIn(week(instant)) aggregate(sum(Compensation.Amount))weeks
Creates a time period of a given number of weeks.
Examples
Shift the context time instant forward by 52 weeks.
Parameter types: (Number)
shift(instant, weeks(52))workingDaysBetween
Creates a numeric property that calculates the number of working days between two time instants. If this is used with Gregorian calendar, it will only consider week days. However, if it's used with custom calendar, it will consider any day where its fullTimeHours is greater than zero.
Examples
Get the number of working days between an employee's start date and effective date, based on the configured calendar.
Parameter types: (Property, Property)
workingDaysBetween(Employee.StartDate, effectiveDate)Get the number of working days between an employee's start date and effective date, based on the Gregorian calendar.
Parameter types: (Property, Property, Number)
workingDaysBetween(Employee.StartDate, effectiveDate, gregorian)wtd
Creates a time interval from the beginning of the week and up to the context time instant. This calculation is based on the configured calendar.
Examples
Get the total compensation for all compensation events that occurred from the beginning of the week and up to the context time instant.
on Compensation occurredIn(wtd) aggregate(sum(Compensation.Amount))Get the total compensation for all compensation events that occurred from the beginning of the week and up to the context time instant.
Parameter types: (TimeInstant)
on Compensation occurredIn(wtd(instant)) aggregate(sum(Compensation.Amount))year
Creates a time period of one year.
Examples
Get the average number of employees for the previous year in monthly counts.
on Employee forInstantsInInterval(interval(instant,back(year)), Month) aggregate averageOverTime(count(EmployeeID))year
Creates a time interval for the year of the time instant. This calculation is based on the configured calendar.
Examples
Get the total compensation for all compensation events that occurred in the year of the context time instant.
Parameter types: (TimeInstant)
on Compensation occurredIn(year(instant)) aggregate(sum(Compensation.Amount))years
Creates a time period of a given number of years.
Examples
Shift the context time instant forward by 1 year.
Parameter types: (Number)
shift(instant, years(1))yearsBetween
Creates a numeric property that calculates the number of years between two time instants.
Examples
Get the number of years between an employee's start date and effective date, based on the Gregorian calendar.
Parameter types: (Property, Property, Number)
yearsBetween(Employee.StartDate, effectiveDate, gregorian)Get the number of years between an employee's start date and effective date.
Parameter types: (Property, Property)
yearsBetween(Employee.StartDate, effectiveDate)ytd
Creates a time interval from the beginning of the year and up to the time instant. This calculation is based on the configured calendar.
Examples
Get the total compensation for all compensation events that occurred from the beginning of the year and up to the context time instant.
on Compensation occurredIn(ytd) aggregate(sum(Compensation.Amount))Get the total compensation for all compensation events that occurred from the beginning of the year and up to the context time instant.
Parameter types: (TimeInstant)
on Compensation occurredIn(ytd(instant)) aggregate(sum(Compensation.Amount))Collections
Use collections functions to create a list of values. For example, you can add a list of regions to a filter so that the query results only show employees with a region that matches a value in your list.
list
Creates a list of values.
Examples
Create a list of department names.
Parameter types: (Value)
list("R&D", "Products")Create a list of locations with each location being added a new list that contains the country, state or province, and city.
Parameter types: (Value)
list(list("Canada", "BC", "Vancouver"), list("USA", "WA", "Seattle"))listAny
Create a list that ignores any invalid symbols. This is used to define an exhaustive list that may contain symbols that are not always valid. This is typically used with sumIfAnyExists.
Operators
Use operators to perform math, comparisons, logical tests, and set variables.
-
Subtracts two values. This operator can be applied to constant values, properties, aggregations, and metrics.
Examples
Get the standard deviation for the difference in employee income between this year and last year.
on Employee aggregate StandardDeviation(Actual_Base_Pay_CC - ShiftBack( Actual_Base_Pay_CC, Year)):=
Assigns intermediary calculated properties and metrics. Reduces duplication and improves readability by allowing you to create intermediary calculated properties and metrics in formulas.
Examples
Get employee bonuses using intermediary variables that assign values to baseBonusAmount, topTalentBonusAmount, and managerBonusAmount.
baseBonusAmount :=Employee.Salary * 0.1
topTalentBonusAmount :=2.0 * baseBonusAmount
managerBonusAmount :=1.5 * baseBonusAmount
if (IsTopTalent) topTalentBonusAmount else if (IsManager) managerBonusAmount else baseBonusAmount Note: The variable is only valid in the formula it's written in, that is, you cannot reference a variable in a different metric formula.
!
Keyword to negate a filter. This is interchangeable with not.
Examples
Get the number of employees that became inactive during the current period.
on Employee lastKnownStateByFilterIn(!isActiveEmployee, interval) filterBy !isActiveEmployee aggregate count(employeeID)!=
Compares two values and returns true if they are not equal.
Examples
Get the number of employees that changed direct managers at least once in the last year.
on changes(Employee) occurredIn interval(year, instant) filterBy previous(Direct_ManagerID)!=Direct_ManagerID aggregate distinct(EmployeeID)*
Multiplies two values. This operator can be applied to constant values, properties, aggregations, and metrics.
Examples
Get the base bonus amount as the employee's salary multiplied by 0.1.
Employee.Salary * 0.1/
Divides two values. This can be applied to constant values, properties, aggregations, and metrics.
Examples
Get the absence ratio for employees by dividing the number of days absent by the number of expected working days.
leaveTotalDaysAbsent / totalExpectedWorkingDays&&
Compares two conditions and returns true if both are true. This is interchangeable with and.
Examples
Get the number of female applicants that were interviewed by female interviewers.
femaleInterviewer :=property(on Interview occurredIn fromBeginningOfTime(instant) filterBy Interviewer.isFemale aggregate count(applicantID)) > 0
on Applicant filterBy Candidate.isFemale && femaleInterviewer aggregate count(applicantID) %
Divides two objects and returns the remainder. This can be applied to constant values, properties, aggregations, and metrics.
Examples
Determine if an employee is having an anniversary this month by checking if the modulo of their tenure in months and 12 is 0.
monthsBetween(Employee.Start_Date, effectiveDate) % 12 = 0+
Adds two values. This can be applied to constant values, properties, aggregations, and metrics.
<
Compares two values and returns true if the one value is less than the other.
Examples
Get the number of applicants who spent less than 10 days in the Applied stage.
Total_Days_in_Applied_Stage :=property(on Applicant validIn fromBeginningOfTime(instant) filterBy include(Normal_Process.Stage, "Applied") aggregate sum(daysInStateUntil(effectiveDate)))
on Applicant filterBy Total_Days_in_Applied_Stage < 10 aggregate count(ApplicantID) <=
Compares two values and returns true if the one value is less than or equal to the other.
Examples
Get the number of employees with two or fewer years of tenure at the organization.
on Employee filterBy Tenure <=2 aggregate count(employeeID)=
Compares two values and returns true if they're equal.
Examples
Get the number of employees whose age is equal to 55.
on Employee filterBy Age=55 aggregate count(EmployeeID)>
Compares two values and returns true if the one value is greater than the other.
Examples
Get the number of employees that conducted interviews during the period by filtering for interview counts greater than 0.
interviewCount :=property(on Interview via Interviewer occurredIn periodOf(instant) aggregate count(applicantID))
on Employee filterBy interviewCount > 0 aggregate count(EmployeeID)>=
Compares two values and returns true if the one value is greater than or equal to the other.
Examples
Get the number of employees whose age is greater than or equal to 65.
on Employee filterBy Age >=65 aggregate count(EmployeeID)||
Compares two conditions and returns true if one condition is true. This is interchangeable with or.
Examples
Get the number of active employees who are either male or female.
on Employee validUntil instant filterBy(isActiveEmployee && (isMale || isFemale)) aggregate count(Employee.EmployeeID)and
Compares two conditions and returns true if both conditions are true. This is interchangeable with &&.
Examples
Get the number of female applicants that were interviewed by female interviewers.
femaleInterviewer :=property(on Interview occurredIn fromBeginningOfTime(instant) filterBy Interviewer.isFemale aggregate count(applicantID)) > 0
on Applicant filterBy Candidate.isFemale AND femaleInterviewer aggregate count(applicantID) not
Compares two values and returns true if they are not equal. This is interchangeable with !.
Examples
Get the number of employees that became inactive during the current period by specifying "not IsActive".
on Employee lastKnownStateByFilterIn(not isActiveEmployee, interval) filterBy not isActiveEmployee aggregate count(employeeID)or
Compares two conditions and returns true if one condition is true. This is interchangeable with ||.